﻿using System;
using System.Data;
using System.Collections.Generic;
using TCKQKL.Service.DataContract;
using TCKQKL.DAL.IDAL;
using TCKQKL.DAL.Converter;
using System.Data.OleDb;

namespace TCKQKL.DAL.Oracle
{
    public class OrdersBosc : IOrdersBosc
    {
        private OleDbConnection _internalConnection;
        private OleDbTransaction _internalADOTransaction = null;

        public OrdersBosc()
        {

        }
        public OrdersBosc(OleDbConnection conn, OleDbTransaction trans)
        {
            _internalConnection = conn;
            _internalADOTransaction = trans;
        }

        public void BeginADOTransaction()
        {
            if (_internalConnection.State != System.Data.ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }
        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new OleDbConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }

        //SQL strings        
        private const string GET_ORDERSRELATED_BYORDERID = "SELECT ORDER_NO, OORB_TEXT, NORB_TEXT, NORC_TEXT, STATUS_TEXT, ORDER_TIME, ORDER_TIME AS FINISHED, ORDER_QTTY, ORDER_PRICE, STOCK_CODE, 0 AS ORDER_FEE, ORG_ORDER_ID, ORDER_ID FROM BOSC.ORDERS_VIEW WHERE  (ORG_ORDER_ID = ?) AND (NORC <> '5') ORDER BY ORDER_ID DESC";
        private const string GET_ORDERSRELATED_HISTORY_BYORDERID = "SELECT ORDER_NO, OORB_TEXT, NORB_TEXT, NORC_TEXT, STATUS_TEXT, ORDER_TIME, ORDER_TIME AS FINISHED, ORDER_QTTY, ORDER_PRICE, STOCK_CODE, 0 AS ORDER_FEE, ORG_ORDER_ID, ORDER_ID FROM BOSC.ORDERS_HISTORY_VIEW WHERE  (ORG_ORDER_ID = ?) AND (NORC <> '5') ORDER BY ORDER_ID DESC";

        private const string GET_MATCHEDRESULT_BYORDERID = "SELECT CONFIRM_NO, STOCK_CODE, STOCK_QTTY, PRICE, FEE_AMOUNT, ORDER_ID, TRANS_DATE, TRANS_TYPE_TEXT FROM BOSC.REPORT_TRADING_RESULT_VIEW WHERE (ORDER_ID = ?)";

        private const string GET_INVESTORID_BYACCOUNTNO = "SELECT  INVESTOR_ID, ACCOUNT_NO FROM BOSC.IRS_INVESTORS_VIEW WHERE ACCOUNT_NO=? AND DELETED=0";


        private const string GET_MATCHEDRESULT_EARLY_BYACCOUNTNO = "";

        //Parameters
        private const string PARM_USERID = "UserId";
        private const string PARM_ACCOUNTNO = "AccountNo";
        private const string PARM_INVESTORID = "Investor_Id";
        private const string PARM_ORDERID = "Order_Id";
        private const string PARM_ORG_ORDER_ID = "Org_Order_Id";
        private const string PARM_ORDERNO = "Order_No";
        private const string PARM_PASSWORD = "Password";
        private const string PARM_FULLNAME = "FullName";
        private const string PARM_ADDRESS = "Address";
        private const string PARM_STARTDATE = "Start";
        private const string PARM_ENDDATE = "End";

        public List<OrdersBoscModel> getOrdersRelatedByOrderId(string originOrderID)
        {
            List<OrdersBoscModel> orders = new List<OrdersBoscModel>();
            try
            {
                OleDbParameter parm = new OleDbParameter(PARM_ORG_ORDER_ID, OleDbType.Decimal, 20);
                parm.Value = originOrderID;
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ORDERSRELATED_BYORDERID, parm);
                while (rdr.Read())
                {
                    OrdersBoscModel order = new OrdersBoscModel(Convert.ToDecimal(rdr["ORDER_ID"]).ToString(), VNConverter.TCVN3ToUnicode(rdr["OORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORC_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["STATUS_TEXT"].ToString()), Convert.ToDateTime(rdr["ORDER_TIME"]), Convert.ToDecimal(rdr["ORDER_QTTY"]), Convert.ToDecimal(rdr["ORDER_PRICE"]), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["ORDER_FEE"]), rdr["ORG_ORDER_ID"].ToString());
                    orders.Add(order);
                }
                rdr.Close();
                return orders;
            }
            catch
            {
                throw;
            }

        }
        public List<OrdersBoscModel> getOrdersRelatedHistoryByOrderId(string originOrderID)
        {
            List<OrdersBoscModel> orders = new List<OrdersBoscModel>();
            try
            {
                OleDbParameter parm = new OleDbParameter(PARM_ORG_ORDER_ID, OleDbType.Decimal, 20);
                parm.Value = originOrderID;
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ORDERSRELATED_HISTORY_BYORDERID, parm);
                while (rdr.Read())
                {
                    OrdersBoscModel order = new OrdersBoscModel(Convert.ToDecimal(rdr["ORDER_ID"]).ToString(), VNConverter.TCVN3ToUnicode(rdr["OORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORC_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["STATUS_TEXT"].ToString()), Convert.ToDateTime(rdr["ORDER_TIME"]), Convert.ToDecimal(rdr["ORDER_QTTY"]), Convert.ToDecimal(rdr["ORDER_PRICE"]), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["ORDER_FEE"]), rdr["ORG_ORDER_ID"].ToString());
                    orders.Add(order);
                }
                rdr.Close();
                return orders;
            }
            catch
            {
                throw;
            }

        }
        public List<MatchedResultModel> getMatchedResultByOrderId(string orderID)
        {
            List<MatchedResultModel> matchedResults = new List<MatchedResultModel>();
            try
            {
                OleDbParameter parm = new OleDbParameter(PARM_ORDERID, OleDbType.VarChar, 20);
                parm.Value = orderID;
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_MATCHEDRESULT_BYORDERID, parm);
                while (rdr.Read())
                {
                    MatchedResultModel result = new MatchedResultModel(rdr["CONFIRM_NO"].ToString(), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["STOCK_QTTY"]), Convert.ToDecimal(rdr["PRICE"]), Convert.ToDecimal(rdr["FEE_AMOUNT"]), rdr["ORDER_ID"].ToString(), Convert.ToDateTime(rdr["TRANS_DATE"]), VNConverter.TCVN3ToUnicode(rdr["TRANS_TYPE_TEXT"].ToString()));
                    matchedResults.Add(result);
                }
                rdr.Close();
                return matchedResults;
            }
            catch
            {
                throw;
            }
        }
        public List<MatchedResultModel> getMatchedResultEarlyByAccountNo(string accountNo)
        {
            List<MatchedResultModel> matchedResults = new List<MatchedResultModel>();
            try
            {
                OleDbParameter parm = new OleDbParameter(PARM_ACCOUNTNO, OleDbType.Decimal, 20);
                parm.Value = accountNo;
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_MATCHEDRESULT_EARLY_BYACCOUNTNO, parm);
                while (rdr.Read())
                {
                    MatchedResultModel result = new MatchedResultModel(rdr["CONFIRM_NO"].ToString(), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["STOCK_QTTY"]), Convert.ToDecimal(rdr["PRICE"]), Convert.ToDecimal(rdr["FEE_AMOUNT"]), rdr["ORDER_ID"].ToString(), Convert.ToDateTime(rdr["TRANS_DATE"]), VNConverter.TCVN3ToUnicode(rdr["TRANS_TYPE_TEXT"].ToString()));
                    matchedResults.Add(result);
                }
                rdr.Close();
                return matchedResults;
            }
            catch
            {
                throw;
            }
        }

        public decimal getOrderFee(OrdersOnlineModel order)
        {
            decimal fee = 0.0035m;
            return fee;
        }
    }
}
